﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class TrustRegionDao 
    {
        private readonly SqlConnection _sqlConnection;
        #region Queries
        private const string SqlTrustRegion = @"
                                    SELECT 
                                        TrustRegionId,
                                        TrustRegionName,
                                        Description,
                                        CASE IsActive WHEN 1 THEN 'Yes' ELSE 'No' END AS IsActive,
                                        C.CountryId,
                                        C.CountryName
                                    FROM TrustRegion R
                                    INNER JOIN Country C
                                        ON R.CountryId = C.CountryId
                                ";
        private const string SqlRetrieveTrustRegion = @"
                                    SELECT TrustRegionId,
                                        TrustRegionName,
                                        Description,
                                        IsActive,
                                        C.CountryId,
                                        C.CountryName
                                    FROM TrustRegion R
                                    INNER JOIN Country C
                                        ON R.CountryId = C.CountryId
                                    WHERE TrustRegionId = @TrustRegionId
                                ";
        private const string SqlCreateTrustRegion = @"
                                    INSERT INTO 
                                    TrustRegion
	                                    (
		                                    TrustRegionName, 
		                                    Description, 
		                                    CountryId, 
		                                    IsActive
	                                    )
                                    VALUES 
	                                    (
		                                    @TrustRegionName, 
		                                    @Description, 
		                                    @CountryId, 
		                                    @IsActive
	                                    )
                                ";
        private const string SqlUpdateTrustRegion = @"
                                    UPDATE TrustRegion
                                    SET
	                                    TrustRegionName = @TrustRegionName,
	                                    Description = @Description, 
	                                    CountryId = @CountryId,
	                                    IsActive = @IsActive
                                    WHERE 
	                                    TrustRegionId = @TrustRegionId
                                ";
        private const string SqlChangeTrustRegionStatus = @"
                                      UPDATE TrustRegion
                                      SET IsActive = @IsActive
                                      WHERE TrustRegionId = @TrustRegionId
                                ";
        private const string SqlCheckTrustRegionIsExists = @"
                                    SELECT COUNT(TrustRegionId)
                                    FROM TrustRegion
                                    WHERE TrustRegionName = @TrustRegionName
                                ";
        #endregion

        /// <summary>
        /// Initializes a new instance of the <see cref="TrustRegionDao"/> class.
        /// </summary>
        public TrustRegionDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        public TrustRegionDao(string connectionString)
        {
            _sqlConnection = new SqlConnection(connectionString);
        }

        #region Methods
        /// <summary>
        /// Selects all.
        /// </summary>
        /// <returns></returns>
        public DataSet SelectAll()
        {
            return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlTrustRegion);
        }
        /// <summary>
        /// Retrieves the trust region.
        /// </summary>
        /// <param name="trustRegionId">The trust region id.</param>
        /// <returns></returns>
        public TrustRegion RetrieveTrustRegion(int trustRegionId)
        {
            if (trustRegionId < 1)
            {
                return null;
            }

            try
            {
                var parameter = new SqlParameter("@TrustRegionId", SqlDbType.Int) { Value = trustRegionId };
                var dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlRetrieveTrustRegion, parameter);
                if (dataSet.Tables[0].Rows.Count < 1)
                {
                    return null;
                }

                var dataRow = dataSet.Tables[0].Rows[0];
                var trustRegion = new TrustRegion
                {
                    TrustRegionId = Convert.ToInt32(dataRow["TrustRegionId"], CultureInfo.CurrentCulture),
                    TrustRegionName = dataRow["TrustRegionName"].ToString(),
                    Description = dataRow["Description"].ToString(),
                    IsActive = Convert.ToBoolean(dataRow["IsActive"], CultureInfo.CurrentCulture),
                    CountryId = Convert.ToInt32(dataRow["CountryId"].ToString(), CultureInfo.CurrentCulture),
                    CountryName = dataRow["CountryName"].ToString()
                };
                return trustRegion;
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }
            catch (InvalidOperationException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
            }

            return null;
        }
        /// <summary>
        /// Creates the trust region.
        /// </summary>
        /// <param name="trustRegion">The trust region.</param>
        /// <returns></returns>
        public int CreateTrustRegion(TrustRegion trustRegion)
        {
            try
            {
                var parameters = new SqlParameter[4];
                parameters[0] = new SqlParameter("@TrustRegionName", SqlDbType.VarChar, 20) { Value = trustRegion.TrustRegionName };
                parameters[1] = new SqlParameter("@Description", SqlDbType.VarChar, 2000) { Value = trustRegion.Description };
                parameters[2] = new SqlParameter("@CountryId", SqlDbType.Int) { Value = trustRegion.CountryId };
                parameters[3] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = trustRegion.IsActive };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlCreateTrustRegion, parameters);
            }
            catch (ArgumentException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }
        /// <summary>
        /// Updates the trust region.
        /// </summary>
        /// <param name="trustRegion">The trust region.</param>
        /// <returns></returns>
        public int UpdateTrustRegion(TrustRegion trustRegion)
        {
            try
            {
                var parameters = new SqlParameter[5];
                parameters[0] = new SqlParameter("@TrustRegionName", SqlDbType.VarChar, 20) { Value = trustRegion.TrustRegionName };
                parameters[1] = new SqlParameter("@Description", SqlDbType.VarChar, 2000) { Value = trustRegion.Description };
                parameters[2] = new SqlParameter("@CountryId", SqlDbType.Int) { Value = trustRegion.CountryId };
                parameters[3] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = trustRegion.IsActive };
                parameters[4] = new SqlParameter("@TrustRegionId", SqlDbType.Int) { Value = trustRegion.TrustRegionId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlUpdateTrustRegion, parameters);
            }
            catch (ArgumentException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }
        /// <summary>
        /// Activates the trust region.
        /// </summary>
        /// <param name="trustRegionId">The trust region id.</param>
        /// <param name="isActive">if set to <c>true</c> [is active].</param>
        /// <returns></returns>
        public int ChangeTrustRegionStatus(int trustRegionId, bool isActive)
        {
            try
            {
                var parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = isActive };
                parameters[1] = new SqlParameter("@TrustRegionId", SqlDbType.Int) { Value = trustRegionId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlChangeTrustRegionStatus, parameters);
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }
        /// <summary>
        /// Checks the trust region is exists.
        /// </summary>
        /// <param name="trustRegionName">Name of the trust region.</param>
        /// <returns></returns>
        public int CheckTrustRegionIsExists(string trustRegionName)
        {
            try
            {
                var parameter = new SqlParameter("@TrustRegionName", SqlDbType.VarChar) { Value = trustRegionName };
                var result = SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, SqlCheckTrustRegionIsExists, parameter);
                return result != null ? (int)result : 0;
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }

        #endregion
    }
}
